package ru.vsu.cs.kis.elections.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ru.vsu.cs.kis.elections.dto.CandidateDto;
import ru.vsu.cs.kis.elections.dto.PrecinctDto;
import ru.vsu.cs.kis.elections.server.db.DBSchema;

public class PrecinctDao {

	private Connection connection;

	public PrecinctDao(Connection con) {
		this.connection = con;
	}

	/** parse request result into precinct's list */
	private ArrayList<PrecinctDto> parsePrecincts(ResultSet requestResult)
			throws SQLException {
		ArrayList<PrecinctDto> result = null;
		if (requestResult == null) {
			return result;
		} else {
			result = new ArrayList<PrecinctDto>();
		}
		PrecinctDto precinct;
		while (requestResult.next()) {
			precinct = new PrecinctDto(
					requestResult.getInt(DBSchema.DBT_PRECINCT_ID),
					requestResult.getString(DBSchema.DBT_PRECINCT_ADDRESS),
					requestResult.getInt(DBSchema.DBT_PRECINCT_STATE),
					requestResult.getString(DBSchema.DBT_PRECINCT_PASSWORD));
			result.add(precinct);
		}
		return result;
	}

	/** select all precincts from DB */
	public ArrayList<PrecinctDto> getAllPrecincts() {
		String query = "select * from precinct";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			rs = p.executeQuery();
			System.out.println("Request success.");
			return parsePrecincts(rs);			
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * check address of precinct in db. If there is new address insert it into
	 * the table
	 */
	private void checkOrRegistr(PrecinctDto precinct) {
		if (!checkPrecinct(precinct)) {
			insertPrecinct(precinct);
		}
	}

	/** check precinct in table */
	private boolean checkPrecinct(PrecinctDto precinct) {
		String query = "select  count(*) from precinct where address=?;";
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setString(1, precinct.getAddress());
			ResultSet rs = p.executeQuery();
			rs.next();
			if (rs.getInt(1) > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/** add precinct into table */
	private boolean insertPrecinct(PrecinctDto item) {
		String query = "insert into precinct(address, password, state) values (?,?,?);";
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setString(1, item.getAddress());
			p.setString(2, item.getPassword());
			p.setInt(3, item.getState());
			Integer res = p.executeUpdate();
			if (res > 0) {
				System.out.println("Insert precinct success.");
				return true;
			}
		} catch (SQLException e) {
			System.out.println("Insert precinct is failed.");
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/**Find or create precinct in table and return it*/
	public PrecinctDto getPrecinct(String address, String password) {
		PrecinctDto pr = new PrecinctDto(address, password);
		checkOrRegistr(pr);
		String query = "select * from precinct where address=? and password=?;";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setString(1, address);
			p.setString(2, password);
			rs = p.executeQuery();
			return parsePrecincts(rs).get(0);
		} catch (SQLException e) {
			System.out.println("Insert precinct is failed.");
			e.printStackTrace();
			return null;
		}
	}

	public boolean vote(PrecinctDto data) {
		if (data.getCandidates() == null) {
			return false;
		}
		CandidateDao candDao = new CandidateDao(connection);
		for (CandidateDto item : data.getCandidates()) {
			if (!candDao.updateVote(item, data.getId())) {
				return false;
			}
		}
		return true;
	}

	public boolean checkPW(String address, String password) {
		String query = "select  count(*) from precinct where  address=? and password=?;";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setString(1, address);
			p.setString(2, password);
			rs = p.executeQuery();
			System.out.println("Request success.");
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		try {
			rs.next();
			if (rs.getInt(1) > 0) {
				return true;
			}
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		return false;
	}

	public boolean checkPW(Integer idPrecinct, String password) {
		String query = "select  count(*) from precinct where  id_prec=? and password=?;";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setInt(1, idPrecinct);
			p.setString(2, password);
			rs = p.executeQuery();
			System.out.println("Request success.");
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		try {
			rs.next();
			if (rs.getInt(1) > 0) {
				return true;
			}
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		return false;
	}

}
